﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using ZEntity.Data;
using ZEntity.Data.Helper;
using ZEntity.WMain.Entity;

namespace ZEntity.WMain
{
    public class Models : BaseDB
    {
        /// <summary>
        /// 获取全部数据库
        /// </summary>
        public static IList<string> GetAllDBs() {
            DataTable dt = DB.ExecuteQuery("sp_databases", CommandType.StoredProcedure);
            IList<string> dbNameList = new List<string>();
            string dbName = string.Empty;
            foreach (DataRow dr in dt.Rows) {
                dbName = ConvertUtils.ToString(dr["DATABASE_NAME"]).ToLower();
                if (dbName == "master" || dbName == "model" || dbName == "msdb" || dbName == "tempdb"){
                    continue;
                }
                dbNameList.Add(ConvertUtils.ToString(dr["DATABASE_NAME"]));
            }
            return dbNameList;
        }

        /// <summary>
        /// 获取全部用户表（已修改数据库连接，已知库）
        /// </summary>
        public static IList<string> GetAllTabels() {
            /*           
                SELECT name FROM sys.all_objects
                WHERE [type] = 'U'
                AND [schema_id] = SCHEMA_ID('dbo') 
                ORDER BY [name] 
             */
            StringBuilder sb = new StringBuilder();   
            sb.Append("SELECT name FROM sys.all_objects ");
            sb.Append("WHERE [type] = 'U' ");
            sb.Append("AND [schema_id] = SCHEMA_ID('dbo') ");
            sb.Append("ORDER BY [name] ");

            DataTable dt = DB.ExecuteQuery(sb.ToString(), CommandType.Text);
            IList<string> tableNameList = new List<string>();
            string tableName = string.Empty;
            foreach (DataRow dr in dt.Rows) {
                tableNameList.Add(ConvertUtils.ToString(dr["name"]));
            }
            return tableNameList;
        }

        /// <summary>
        /// 获取全部列信息列表按表名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>列信息列表</returns>
        public static IList<TableColumnInfo> GetAllColumnInfoByTableName(string tableName) {
            /*
                SELECT
                TableName = B.[name],
                ColumnName = A.[name],
                [Length] = CASE WHEN C.[name] = 'nvarchar' THEN A.[max_length]/2
				                ELSE A.[max_length]
				                END,
                TypeName = C.[name],
                Nullable = A.is_nullable,
                IsPrimaryKey = CASE WHEN SysIndex.is_primary_key = 1 THEN 1 ELSE 0 END,
                [Description] = D.value,
                TableDescription = E.value
                FROM sys.columns A
                LEFT JOIN sys.objects B
                ON A.object_id = B.object_id
                LEFT JOIN sys.types C
                ON A.user_type_id = C.user_type_id
                LEFT JOIN 
                (
                SELECT AA.is_primary_key,
                AA.object_id, BB.column_id
                FROM sys.indexes AA
                JOIN sys.index_columns BB
                ON AA.object_id = BB.object_id
                AND AA.index_id = BB.index_id
                ) SysIndex
                ON A.object_id = SysIndex.object_id
                AND A.column_id = SysIndex.column_id
                LEFT JOIN sys.extended_properties D
                ON D.class = 1
                AND A.object_id = D.major_id
                AND A.column_id = D.minor_id
                LEFT JOIN sys.extended_properties E
                ON E.class = 1
                AND B.object_id = E.major_id
                AND E.minor_id = 0
                WHERE B.[name] = 'Customer'
             */
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ");
            sb.Append("TableName = B.[name], ");
            sb.Append("ColumnName = A.[name], ");
            sb.Append("[Length] = CASE WHEN C.[name] = 'nvarchar' THEN A.[max_length]/2 ");
            sb.Append("ELSE A.[max_length] ");
            sb.Append("END, ");
            sb.Append("TypeName = C.[name], ");
            sb.Append("Nullable = A.is_nullable, ");
            sb.Append("IsPrimaryKey = CASE WHEN SysIndex.is_primary_key = 1 THEN 1 ELSE 0 END, ");
            sb.Append("[Description] = D.value, ");
            sb.Append("TableDescription = E.value ");
            sb.Append("FROM sys.columns A ");
            sb.Append("LEFT JOIN sys.objects B ");
            sb.Append("ON A.object_id = B.object_id ");
            sb.Append("LEFT JOIN sys.types C ");
            sb.Append("ON A.user_type_id = C.user_type_id ");
            sb.Append("LEFT JOIN ");
            sb.Append("( ");
            sb.Append("SELECT AA.is_primary_key, ");
            sb.Append("AA.object_id, BB.column_id ");
            sb.Append("FROM sys.indexes AA ");
            sb.Append("JOIN sys.index_columns BB ");
            sb.Append("ON AA.object_id = BB.object_id ");
            sb.Append("AND AA.index_id = BB.index_id ");
            sb.Append(") SysIndex ");
            sb.Append("ON A.object_id = SysIndex.object_id ");
            sb.Append("AND A.column_id = SysIndex.column_id ");
            sb.Append("LEFT JOIN sys.extended_properties D ");
            sb.Append("ON D.class = 1 ");
            sb.Append("AND A.object_id = D.major_id ");
            sb.Append("AND A.column_id = D.minor_id ");          
            sb.Append("LEFT JOIN sys.extended_properties E ");
            sb.Append("ON E.class = 1 ");
            sb.Append("AND B.object_id = E.major_id ");
            sb.Append("AND E.minor_id = 0 ");
            sb.AppendFormat("WHERE B.[name] = '{0}' ", tableName);
            return QueryForListBySQL<TableColumnInfo>(sb.ToString());
        }

        public static void Test() {
            //myAssembly.Customer customer = new myAssembly.Customer();
            //customer.u
            //Insert<myAssembly.Customer>();
        }
    }
}
